import pandas as pd
import re
import requests
from fastcache import lru_cache
from .utils. newsengine import news_engine
import datetime as dt
today=dt.datetime.now().strftime('%Y%m%d')

class News:
    def __init__(self):
        print('已经与华创金工舆情库开启互联')

    def __repr__(self):
        return f"{self.user}&{self.pwd}"

    @lru_cache()
    def get(self,
            ticker=None,
            trade_dt=None,
            begin_dt=None,
            end_dt=None,
            kwds=None,
            strength=0.5,
            unique=False):
        try:
            if ticker is None:
                sql = """
                           SELECT *
                            FROM
                            yh.article
                            WHERE
                            id IN (
                            SELECT
                            c.id
                            FROM
                            (
                            SELECT
                            article_id AS id
                            FROM
                            yh.article_stock
                            WHERE
                              pos_neg=-1 and pos_neg_score>{1}
                            ) c
                            )
                            AND id = similar_group_id
                            and title is not null 
                            and site!='新浪微博'
                            and publish_time >='{0}'
                            ORDER BY
                            publish_time DESC
                """.format(today, strength)
                # print(sql)
            elif trade_dt is not None:
                sql = """
                        SELECT *
                        FROM
                        yh.article
                        WHERE
                        id IN (
                        SELECT
                        c.id
                        FROM
                        (
                        SELECT
                        article_id AS id
                        FROM
                        yh.article_stock
                        WHERE
                        stock_code = '{0}' and pos_neg=-1 and pos_neg_score>{2}
                        ) c
                        )
                        AND id = similar_group_id
                        and title is not null 
                        and site!='新浪微博'
                        and publish_time >='{1}'
                        ORDER BY
                        publish_time DESC
                """.format(ticker,trade_dt,strength)
            elif begin_dt is not None:
                if end_dt is None:
                    sql = """
                            SELECT *
                            FROM
                            yh.article
                            WHERE
                            id IN (
                            SELECT
                            c.id
                            FROM
                            (
                            SELECT
                            article_id AS id
                            FROM
                            yh.article_stock
                            WHERE
                            stock_code = '{0}' and pos_neg=-1 and pos_neg_score>{2}
                            ) c
                            )
                            AND id = similar_group_id
                            and title is not null 
                            and site!='新浪微博'
                            and publish_time >='{1}'
                            ORDER BY
                            publish_time DESC
                    """.format(ticker, begin_dt,strength)
                    # print(sql)
                else:
                    sql = """
                            SELECT *
                            FROM
                            yh.article
                            WHERE
                            id IN (
                            SELECT
                            c.id
                            FROM
                            (
                            SELECT
                            article_id AS id
                            FROM
                            yh.article_stock
                            WHERE
                            stock_code = '{0}' and pos_neg=-1 and pos_neg_score>{3}
                            ) c
                            )
                            AND id = similar_group_id
                            and title is not null 
                            and site!='新浪微博'
                            and publish_time >='{1}'
                            and publish_time <='{2}'
                            ORDER BY
                            publish_time DESC
                    """.format(ticker, begin_dt, end_dt, strength)
                    # print(sql)
            df = pd.read_sql(sql, news_engine)
            if kwds is not None:
                if isinstance(kwds, str):
                    df['ind'] = df.title.apply(lambda x: x.find(kwds))
                    return df[df.ind != -1]
                else:
                    raise ValueError('HCWarning：Kwd需要为字符串！')
            else:
                return df
        except:
            raise ValueError('数据库未配置成功，请联系wangxiaochuan@hcyjs.com')

if __name__ == '__main__':
    pass